﻿using Domain.Entity;
using Domain.Repository;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace EFCore.Repository
{
    public class UserRepository : IUserRepository
    {
		//      private readonly MyDbContext myDbContext;

		//      public UserRepository(MyDbContext myDbContext)
		//      {
		//          this.myDbContext = myDbContext;
		//      }
		//      public List<User> SelectUser(User user)
		//      {
		//          string sql = $"select * from [dbo].[User] where UName='{user.UName}' and UPassWord='{user.UPassWord}'";
		//          using (DbConnection con = myDbContext.Database.GetDbConnection())
		//          {
		//              List<User> res = con.Query<User>(sql).ToList();

		//              return res;
		//          }
		//      }

		//      public List<User> UserGet()
		//      {
		//          using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
		//          {
		//              string sql = "select * from [dbo].[User]";
		//              return connection.Query<User>(sql).ToList();
		//          }

		//      }
		//      public bool UserDelete(User user)
		//      {
		//          string sql = $"Delete from [dbo].[User] where UId={user.UId}";
		//          return myDbContext.Database.ExecuteSqlRaw(sql) > 0;
		//      }

		//      public bool UserUpdate(User user)
		//      {
		//          string sql = $"update [dbo].[User] set UName='{user.UName}',UTrueName='{user.UTrueName}',UPassWord='{user.UPassWord}',Uidentity='{user.Uidentity}' where UId={user.UId}";
		//          return myDbContext.Database.ExecuteSqlRaw(sql) > 0;
		//      }

		//      public bool UserAdd(User user)
		//      {
		//          string sql = $"INSERT INTO [dbo].[User] (UName,UTrueName,UPassWord,Uidentity) VALUES  ('{user.UName}','{user.UTrueName}','{user.UPassWord}','{user.Uidentity}');";
		//          return myDbContext.Database.ExecuteSqlRaw(sql) > 0;
		//      }

		//      public List<Trees2> trees(string id)
		//      {
		//          string sql = $"select b.JuriID, JurName,GroupID, JurAddress from dbo.RoleJurisdiction a\r\n\t\tinner join dbo.Jurisdiction b on a.JuriID=b.JuriID\r\ninner join dbo.UserRole c on a.RolesID=c.RolesID\r\ninner join [Users] d on c.UserID=d.UId where UId={id} and GroupID=0 ";
		//          using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
		//          {
		//              List<Jurisdiction> result = connection.Query<Jurisdiction>(sql).ToList();
		//              List<Trees2> trees = new List<Trees2>();
		//              foreach (Jurisdiction item in result)
		//              {
		//                  Trees2 trees1 = new Trees2()
		//                  {
		//                      id = item.JuriID,
		//                      label = item.JurName,
		//                      urls = item.JurAddress,
		//                      children = CreateTree(item.JuriID, id)
		//                  };

		//                  trees.Add(trees1);
		//              }
		//              return trees;
		//          }
		//      }
		//private List<Trees2> CreateTree(int v, string id)
		//{
		//	string sql = $"select b.JuriID, JurName,GroupID, JurAddress from dbo.RoleJurisdiction a\r\n\t\tinner join dbo.Jurisdiction b on a.JuriID=b.JuriID\r\ninner join dbo.UserRole c on a.RolesID=c.RolesID\r\ninner join [Users] d on c.UserID=d.UId where UId={id} and GroupID={v}";
		//	using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
		//	{
		//		List<Jurisdiction> cFSKs = connection.Query<Jurisdiction>(sql).ToList();
		//		List<Trees2> trees = new List<Trees2>();
		//		foreach (Jurisdiction item in cFSKs)
		//		{
		//			Trees2 trees1 = new Trees2()
		//			{
		//				id = item.JuriID,
		//				label = item.JurName,
		//				urls = item.JurAddress,
		//				children = CreateTree(item.JuriID, id)
		//			};

		//			trees.Add(trees1);
		//		}
		//		return trees;
		//	}

		//}

		//public List<Jurisdiction> trees2()
		//{
		//	using (SqlConnection conn = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
		//	{
		//		string sql = "select * from [dbo].[Jurisdiction] where GroupID=0";
		//		List<Jurisdiction> list = new List<Jurisdiction>();
		//		list = conn.Query<Jurisdiction>(sql).ToList();
		//		foreach (var item in list)
		//		{
		//			string sql1 = $"select * from [dbo].[Jurisdiction] where GroupID={item.JuriID}";
		//			item.children = conn.Query<Jurisdiction>(sql1).ToList();
		//			foreach (var iten1 in item.children)
		//			{
		//				string sql2 = $"select * from [dbo].[Jurisdiction] where GroupID={iten1.JuriID}";
		//				iten1.children = conn.Query<Jurisdiction>(sql2).ToList();
		//			}
		//		}
		//		return list;
		//	}
		//}
		public readonly MyDbContext myDbContext;

		public UserRepository(MyDbContext myDbContext)
		{
			this.myDbContext = myDbContext;
		}

		public List<User> SelectUser(User user)
		{
			string sql = $"select * from [dbo].[User] where UName='{user.UName}' and UPassWord='{user.UPassWord}'";
			using (DbConnection con = myDbContext.Database.GetDbConnection())
			{
				List<User> res = con.Query<User>(sql).ToList();

				return res;
			}
		}

		public List<User> UserGet()
		{
			using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = "select * from [dbo].[User]";
				return connection.Query<User>(sql).ToList();
			}

		}
		public bool UserDelete(User user)
		{
			string sql = $"Delete from [dbo].[User] where UId={user.UId}";
			return myDbContext.Database.ExecuteSqlRaw(sql) > 0;
		}

		public bool UserUpdate(User user)
		{
			string sql = $"update[dbo].[User] set UName='{user.UName}',UTrueName='{user.UTrueName}',UPassWord='{user.UPassWord}',Uidentity='{user.Uidentity}' where UId={user.UId}";
			return myDbContext.Database.ExecuteSqlRaw(sql) > 0;
		}
		public List<CPC> GetName(CPC cPCDto)
		{
			string sql = "select * from dbo.CPC where AttributeKind='用户身份'";
			List<CPC> jh = myDbContext.CPC.FromSqlRaw(sql).ToList();
			return myDbContext.CPC.FromSqlRaw(sql).ToList();
		}
		public List<User> GetUsers(int id)
		{
			string sql = $"select * from [dbo].[User] where UId={id}";
			List<User> res = myDbContext.User.FromSqlRaw(sql).ToList();

			return res;
		}

		public bool UserAdd(User user)
		{
			string sql = $"INSERT INTO [dbo].[User] (UName,UTrueName,UPassWord,Uidentity) VALUES  ('{user.UName}','{user.UTrueName}','{user.UPassWord}','{user.Uidentity}');";
			return myDbContext.Database.ExecuteSqlRaw(sql) > 0;
		}

		public List<Roles> RolesSelect()
		{
			using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = "select * from [dbo].[Roles]";
				return connection.Query<Roles>(sql).ToList();
			}
		}

		public bool AddRoles(Roles roles)
		{
			using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = $"insert into Roles(RolesName, RolesInstruction, RolesIf) values('{roles.RolesName}','{roles.RolesInstruction}','{roles.RolesIf}')";
				return connection.Execute(sql) > 0;
			}
		}

		public bool DeleteRoles(int id)
		{
			using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = $"delete from [dbo].[Roles] where RolesID={id}";
				return connection.Execute(sql) > 0;
			}
		}
		public List<Trees2> trees(string id)
		{
			string sql = $"select b.JuriID, JurName,GroupID, JurAddress from dbo.RoleJurisdiction a\r\n\t\tinner join dbo.Jurisdiction b on a.JuriID=b.JuriID\r\ninner join dbo.UserRole c on a.RolesID=c.RolesID\r\ninner join [User] d on c.UserID=d.UId where UId={id} and GroupID=0 ";
			using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				List<Jurisdiction> result = connection.Query<Jurisdiction>(sql).ToList();
				List<Trees2> trees = new List<Trees2>();
				foreach (Jurisdiction item in result)
				{
					Trees2 trees1 = new Trees2()
					{
						id = item.JuriID,
						label = item.JurName,
						urls = item.JurAddress,
						children = CreateTree(item.JuriID, id)
					};

					trees.Add(trees1);
				}
				return trees;
			}

		}
		private List<Trees2> CreateTree(int v, string id)
		{
			string sql = $"select b.JuriID, JurName,GroupID, JurAddress from dbo.RoleJurisdiction a\r\n\t\tinner join dbo.Jurisdiction b on a.JuriID=b.JuriID\r\ninner join dbo.UserRole c on a.RolesID=c.RolesID\r\ninner join [User] d on c.UserID=d.UId where UId={id} and GroupID={v}";
			using (SqlConnection connection = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				List<Jurisdiction> cFSKs = connection.Query<Jurisdiction>(sql).ToList();
				List<Trees2> trees = new List<Trees2>();
				foreach (Jurisdiction item in cFSKs)
				{
					Trees2 trees1 = new Trees2()
					{
						id = item.JuriID,
						label = item.JurName,
						urls = item.JurAddress,
						children = CreateTree(item.JuriID, id)
					};

					trees.Add(trees1);
				}
				return trees;
			}

		}

		public async Task<List<Jurisdiction>> JurisdictionList()
		{
			using (SqlConnection conn = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = "select * from [dbo].[Jurisdiction] where GroupID=0";
				List<Jurisdiction> list = new List<Jurisdiction>();
				list = conn.Query<Jurisdiction>(sql).ToList();
				foreach (var item in list)
				{
					string sql1 = $"select * from [dbo].[Jurisdiction] where GroupID={item.JuriID}";
					item.children = conn.Query<Jurisdiction>(sql1).ToList();
					foreach (var iten1 in item.children)
					{
						string sql2 = $"select * from [dbo].[Jurisdiction] where GroupID={iten1.JuriID}";
						iten1.children = conn.Query<Jurisdiction>(sql2).ToList();
					}
				}
				return list;
			}
		}

		public List<Jurisdiction> trees2()
		{

			using (SqlConnection conn = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = "select * from [dbo].[Jurisdiction] where GroupID=0";
				List<Jurisdiction> list = new List<Jurisdiction>();
				list = conn.Query<Jurisdiction>(sql).ToList();
				foreach (var item in list)
				{
					string sql1 = $"select * from [dbo].[Jurisdiction] where GroupID={item.JuriID}";
					item.children = conn.Query<Jurisdiction>(sql1).ToList();
					foreach (var iten1 in item.children)
					{
						string sql2 = $"select * from [dbo].[Jurisdiction] where GroupID={iten1.JuriID}";
						iten1.children = conn.Query<Jurisdiction>(sql2).ToList();
					}
				}
				return list;
			}

		}

		public async Task<List<int>> selectRolesJurisdiction(int id)
		{
			using (SqlConnection conn = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False;"))
			{
				string sql = $"SELECT [JuriID] FROM dbo.RoleJurisdiction where RolesID={id}";
				var resu = await conn.QueryAsync<int>(sql);
				return resu.ToList();
			}
		}

		public List<Roles> Shwo(int id)
		{

			using (SqlConnection conn = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False"))
			{
				string sql = $"select * from dbo.Roles where RolesID={id}";
				return conn.Query<Roles>(sql).ToList();
			}
		}

		public bool UpdateRole(string RolesName, string RolesInstruction, int RolesIf, string JuriID, int RolesID)
		{
			using (SqlConnection conn = new SqlConnection("server=.;database=HR;user=sa;pwd=cssl#123;Encrypt=False"))
			{
				string[] strArray = JuriID.Split(',');
				string sql = $"update dbo.Roles set RolesName='{RolesName}',RolesInstruction='{RolesInstruction}',RolesIf={RolesIf} WHERE RolesID={RolesID}";
				string sql2 = $"delete from dbo.RoleJurisdiction where RolesID={RolesID}";

				string sql3 = $"insert into dbo.RoleJurisdiction(RolesID, JuriID) values";

				if (conn.Execute(sql) > 0)
				{
					if (conn.Execute(sql2) > 0)
					{
						for (int i = 0; i < strArray.Length; i++)
						{
							sql3 += $" ({RolesID},{strArray[i]}),";
						}
						string sql4 = sql3.Substring(0, sql3.Length - 1);
						return conn.Execute(sql4) > 0;
					}
				}

			}
			return false;
		}
	}

}
